
--
--	Validate load data and compute statistics
--
create or replace function validate_load(imported_file bigint) returns void as $$
declare
	the_organization int4;
begin

	-- Fetch organization
	select id_organization into the_organization from imported_files where id_file=imported_file;

	-- Reset header validation status, everything is valid
	update survey_answer_headers_imported set id_status=1 where id_file=imported_file;

	-- Update id_survey, necessary for identity de-duplication
	update survey_answer_headers_imported shi set id_survey=s.id_survey
	from surveys s, imported_files f
	where shi.id_file=f.id_file and s.id_organization=f.id_organization and shi.survey_code=s.survey_code
	and f.id_file = imported_file;

	-- Identify duplicates against imported data
	update survey_answer_headers_imported shi set id_status=2
	from (
			select h.entity_id, h.id_survey, count(*) as num
			from survey_answer_headers h, surveys s
			where h.id_survey=s.id_survey and s.id_organization = the_organization
			group by h.entity_id, h.id_survey
	) a
	where shi.entity_id=a.entity_id and shi.id_survey=a.id_survey
	and shi.id_file = imported_file;

	-- Identify duplicates against not-yet-imported data
	update survey_answer_headers_imported shi set id_status=2
	from
	(
			select hi.entity_id, hi.id_survey, min(hi.id_header) as id_header
			from survey_answer_headers_imported hi
			group by hi.entity_id, hi.id_survey
	) a
	where shi.entity_id=a.entity_id and shi.id_header>a.id_header and shi.id_survey = a.id_survey and shi.id_status=1
	and shi.id_file = imported_file;

  -- Update id_action
	update survey_answer_headers_imported shi set id_action=a.id_action
	from training_actions a
	where shi.id_file=imported_file and a.id_organization=the_organization and shi.action_code=a.action_code;


	-- Validate header
	update survey_answer_headers_imported shi set id_status=0
	where id_status = 1 and shi.id_file=imported_file and (
		shi.entity_id is null
	or
		is_nif(shi.entity_id) = false 
	or
		not exists (select 1 from councils cn where cn.id_council=shi.id_council)
	or
		(shi.id_survey is null or shi.creation_date is null or shi.id_action is null)
	);

	-- Expire headers that have CLOSED or ARCHIVED surveys
	update survey_answer_headers_imported shi set id_status=3
	from surveys s
	where s.id_survey=shi.id_survey and shi.id_file=imported_file
	and s.id_survey_lifecycle >= 3;

	-- Reset answer validation status, everything is valid
	update survey_unit_answers_imported sui set id_status=1
	from survey_answer_headers_imported shi
	where sui.id_header = shi.id_header and shi.id_file= imported_file;

	-- Identify duplicates in survey unit answers
	update survey_unit_answers_imported sui set id_status=2
	from survey_answer_headers_imported shi, (
			select su.id_header, su.question_code, sh.id_file, min(su.question_number) as question_number
			from survey_unit_answers_imported su, survey_answer_headers_imported sh
			where su.id_header=sh.id_header
			group by su.id_header, su.question_code, sh.id_file
	) a
	where sui.id_header=shi.id_header and
	sui.id_header=a.id_header and shi.id_file=a.id_file and sui.question_code=a.question_code and
	sui.question_number>a.question_number and shi.id_file = imported_file and length(coalesce(sui.question_code, '')) > 0;

	-- Update id_question
	update survey_unit_answers_imported sui set id_question=q.id_question
	from survey_answer_headers_imported shi, questions q, surveys s
	where sui.id_header=shi.id_header and shi.id_survey=s.id_survey and q.id_survey=s.id_survey
	and q.question_code=sui.question_code and shi.id_file = imported_file;

	-- Validate unit questions
	update survey_unit_answers_imported sui set id_status=0
	from survey_answer_headers_imported shi
	where shi.id_header=sui.id_header and sui.id_question is null and sui.id_status=1 and shi.id_file = imported_file;

	-- Compute statistics and update file status: VALIDATED
	update imported_files fs set
			first_date=a.min_creation_date,
			last_date=a.max_creation_date,
			total_records=coalesce(b.recs, 0),
			correct_records=coalesce(c.recs, 0),
			warned_records=coalesce(d.recs, 0),
			id_state=2
	from imported_files f left join (
			select  id_file,
							max(creation_date)  as max_creation_date,
							min(creation_date) as min_creation_date
			from survey_answer_headers_imported
			group by id_file
	) a on a.id_file=f.id_file left join
	(
			select id_file, count(*) as recs
			from survey_answer_headers_imported
			group by id_file
	) b on b.id_file=f.id_file left join
	(
			select id_file, count(*) as recs
			from survey_answer_headers_imported
			where id_status=1
			group by id_file
	) c on c.id_file=f.id_file left join
	(
			select id_file, count(*) as recs
			from survey_answer_headers_imported sh
			where exists
			(
					select 1 from survey_unit_answers_imported su
					where su.id_header=sh.id_header and su.id_status<>1
			)
			group by id_file
	) d on d.id_file=f.id_file
	where
			fs.id_file=f.id_file and
			f.id_file = imported_file;
end;
$$ language plpgsql;



--
--		Clear load data
--
create or replace function clear_load(imported_file bigint) returns void as $$
begin

	-- Remove unit answers
	delete from survey_unit_answers_imported su using survey_answer_headers_imported h
	where su.id_header=h.id_header and h.id_file = imported_file;

	-- Remove headers
	delete from survey_answer_headers_imported h
	where h.id_file = imported_file;

	-- Update file status: DISCARDED
	update imported_files set id_state=3 where id_file=imported_file;

end;
$$ language plpgsql;


--
--		Import load data
--
create or replace function import_load(imported_file bigint) returns void as $$
declare
	the_import_date date;
begin
	-- Get date
	the_import_date := current_date;

	-- Load headers
	insert into survey_answer_headers(id_header, entity_id, internet, creation_date, import_date,
															 id_origin, id_council, id_survey, id_action, id_file)
	select 	nextval('survey_answer_headers_seq'), h.entity_id, h.internet, h.creation_date, the_import_date,
					h.id_origin, h.id_council, h.id_survey, h.id_action, h.id_file
	from survey_answer_headers_imported h
	where h.id_status=1 and h.id_file=imported_file;

	-- Load unit answers
	insert into survey_unit_answers(id_header, id_question, training_preference, covered)
	select i.id_header, u.id_question, u.training_preference, false
	from survey_answer_headers_imported h, survey_unit_answers_imported u, survey_answer_headers i
	where
		h.id_header=u.id_header and h.id_status=1 and u.id_status=1 and
		h.entity_id=i.entity_id and h.id_file=i.id_file and h.id_survey=i.id_survey and h.id_file = imported_file;

	-- Remove unit answers
	delete from survey_unit_answers_imported su using survey_answer_headers_imported h
	where su.id_header=h.id_header and h.id_file = imported_file;

	-- Remove headers
	delete from survey_answer_headers_imported h
	where h.id_file = imported_file;

	-- Update file status: IMPORTED
	update imported_files set id_state=4 where id_file=imported_file;


end;
$$ language plpgsql;



